<?php
class ExcelInOutD extends CApplicationComponent {
    public $phpExcelPath = '';
    function __construct() {
        $this->phpExcelPath = Yii::getPathOfAlias('ext');
        spl_autoload_unregister(array('YiiBase', 'autoload'));
        require_once ($this->phpExcelPath . DIRECTORY_SEPARATOR . 'PHPExcel.php');
    }

    function __destruct() {
        spl_autoload_register(array('YiiBase', 'autoload'));
    }

    /**
     * excel 导入到php数组
     * @param type $fullPath 物理全路径
     * @param type $fields 字段名称与excel列号的对应数组，如 array('id'=>'A','name'=>'B')
     * @return type 返回键值对记录集
     */
    public function inExcel($fullPath, $fields=array(),$count=200) {
        //$filePath = $fileinfo['fileField'][0]['dir'] . $fileinfo['fileField'][0]['name'];
        $PHPExcel = new PHPExcel();
        $PHPReader = new PHPExcel_Reader_Excel2007();
        if (!$PHPReader->canRead($fullPath)) {
            $PHPReader = new PHPExcel_Reader_Excel5();
            if (!$PHPReader->canRead($fullPath)) {
                unlink($fullPath);
                $PHPExcel->disconnectWorksheets();
                echo 'no Excel';
                exit;
            }
        }
        $PHPExcel = $PHPReader->load($fullPath);
        $currentSheet = $PHPExcel->getSheet(0);
        $allRow = $currentSheet->getHighestRow();
        $rows = array();
        $i = 0;
        for ($currentRow = 2; $currentRow <= $allRow; $currentRow++) {
            foreach ($fields as $key => $value) {
                $rows[$i][$key] = trim($currentSheet->getCell($value . $currentRow)->getValue());
            }
            if (isset($rows[$i]['mobile']) && !is_numeric($rows[$i]['mobile'])){
            	unset($rows[$i]); 
            } else {
                $i++;
            }
            if($i>=$count){break;}
        }
        $PHPExcel->disconnectWorksheets();
        unlink($fullPath);
        return  $rows;
    }
    
    /**
     * 导出到excel  支持多个工作表  
     * @param type $tableHead excel表头与数库字段的对应,如 array('id'=>'ID序列号','name'='名称')
     * @param type $rowArray 数据列表数组  二维数组 ，用于不同的工作表
     */
    public function outExcelArray($tableHead = array(), $rowArray = array(),$title=array(), $filename='excel.xls') {
    	//error_reporting(E_ALL);
    	$objPHPExcel = new PHPExcel ();
    	$objPHPExcel->getProperties()->setCreator("店连店")->setLastModifiedBy("店连店")->setTitle("我的excel文档")->setSubject("文档导出测试")->setDescription("文档导出测试.")->setKeywords("office 2007 PHP数据")->setCategory("文档导出测试");

    	
    	$y=0;
    	foreach ($rowArray as $key=>$row) {
    		if ($y){
    			$objPHPExcel->createSheet()->setTitle($title[$key]);
    			$objPHPExcel->setActiveSheetIndex($y);
    			$objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(15);
    			$Letter = Array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z','AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ');
    			$i = 0;
    			foreach ($tableHead as $headKey => $headValue) {
    				$objPHPExcel->getActiveSheet()->setCellValue("$Letter[$i]1", "$headValue");
    				$i++;
    			}
    		}else{
    			$objPHPExcel->getActiveSheet()->setTitle($title[$key]);
    			$objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(15);
    			$Letter = Array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z','AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ');
    			$i = 0;
    			foreach ($tableHead as $headKey => $headValue) {
    				$objPHPExcel->getActiveSheet()->setCellValue("$Letter[$i]1", "$headValue");
    				$i++;
    			}
    		}
    		$j = 2;
    		foreach ($row as $rows){
	    		$i = 0;
	    		foreach ($tableHead as $headKey => $headValue) {
	    			if (isset($rows[$headKey])){
	    				$objPHPExcel->getActiveSheet()->setCellValueExplicit("$Letter[$i]$j", "$rows[$headKey]", PHPExcel_Cell_DataType::TYPE_STRING);
	    				$i++;
	    			}
	    		}
	    		$j++;
    		}
    		
    		
    		
    		$objPHPExcel->getActiveSheet($y)->getDefaultColumnDimension()->setWidth('20');
    		$objPHPExcel->getActiveSheet($y)->getStyle('A1:AZ1')->getFont()->setBold(true);
    		//    $objPHPExcel->getActiveSheet()->setCellValue('A2', "小风");
    		//    $objPHPExcel->getActiveSheet()->setCellValue('B2', "wang");
    		//    $objPHPExcel->getActiveSheet()->getCell('I')->setValueExplicit('861391327543258', PHPExcel_Cell_DataType::TYPE_STRING);
    		//    $objPHPExcel->getActiveSheet()->setCellValue('G2', '2008-12-31');
    		//    $objPHPExcel->getActiveSheet()->getStyle('G2')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDDSLASH);
    		//    $time = gmmktime(0, 0, 0, 12, 31, 2008);
    		//    $objPHPExcel->getActiveSheet()->setCellValue('G3', PHPExcel_Shared_Date::PHPToExcel($time));
    		//    $objPHPExcel->getActiveSheet()->freezePane('A2');
    		// $objPHPExcel->getActiveSheet()->getProtection()->setPassword('123123');
    		$objPHPExcel->getActiveSheet($y)->getProtection()->setSheet(true);
    		$objPHPExcel->getActiveSheet($y)->getProtection()->setSort(true);
    		$objPHPExcel->getActiveSheet($y)->getProtection()->setInsertRows(true);
    		$objPHPExcel->getActiveSheet($y)->getProtection()->setFormatCells(true);
    		
    		$y++;
    	}
    	$objPHPExcel->setActiveSheetIndex(0);
    	
    	
    	header("Cache-Control:must-revalidate,post-check=0,pre-check=0");
    	header('Content-Type: application/vnd.ms-excel');
    	header('Content-Disposition: attachment;filename="' . $filename . '"');
    	header('Cache-Control: max-age=0');
    	$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
    	$objWriter->save('php://output');
    	Yii::app()->end();
    }
    

    /**
     * 导出到excel
     * @param type $tableHead excel表头与数库字段的对应,如 array('id'=>'ID序列号','name'='名称')
     * @param type $rowArray 数据列表数组
     */
    public function outExcel($tableHead = array(), $rowArray = array(), $filename='excel.xls') {
        //error_reporting(E_ALL);
        $objPHPExcel = new PHPExcel ();
        $objPHPExcel->getProperties()->setCreator("店连店")->setLastModifiedBy("店连店")->setTitle("我的excel文档")->setSubject("文档导出测试")->setDescription("文档导出测试.")->setKeywords("office 2007 PHP数据")->setCategory("文档导出测试");
        $objPHPExcel->setActiveSheetIndex(0);
        $objPHPExcel->getActiveSheet()->setTitle('数据导出');
        $objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(15);
        $Letter = Array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z','AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ');
        $i = 0;
        foreach ($tableHead as $headKey => $headValue) {
            $objPHPExcel->getActiveSheet()->setCellValue("$Letter[$i]1", "$headValue");
            $i++;
        }
        $j = 2;
        foreach ($rowArray as $rows) {
            $i = 0;
            foreach ($tableHead as $headKey => $headValue) {
            	if (isset($rows[$headKey])){
	                $objPHPExcel->getActiveSheet()->setCellValueExplicit("$Letter[$i]$j", "$rows[$headKey]", PHPExcel_Cell_DataType::TYPE_STRING);
	                $i++;
            	}
            }
            $j++;
        }
        $objPHPExcel->getActiveSheet()->getDefaultColumnDimension()->setWidth('20');
        $objPHPExcel->getActiveSheet()->getStyle('A1:AZ1')->getFont()->setBold(true);
        //    $objPHPExcel->getActiveSheet()->setCellValue('A2', "小风");
        //    $objPHPExcel->getActiveSheet()->setCellValue('B2', "wang");
        //    $objPHPExcel->getActiveSheet()->getCell('I')->setValueExplicit('861391327543258', PHPExcel_Cell_DataType::TYPE_STRING);
        //    $objPHPExcel->getActiveSheet()->setCellValue('G2', '2008-12-31');
        //    $objPHPExcel->getActiveSheet()->getStyle('G2')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDDSLASH);
        //    $time = gmmktime(0, 0, 0, 12, 31, 2008);
        //    $objPHPExcel->getActiveSheet()->setCellValue('G3', PHPExcel_Shared_Date::PHPToExcel($time));
        //    $objPHPExcel->getActiveSheet()->freezePane('A2');
       // $objPHPExcel->getActiveSheet()->getProtection()->setPassword('123123');
        $objPHPExcel->getActiveSheet()->getProtection()->setSheet(true);
        $objPHPExcel->getActiveSheet()->getProtection()->setSort(true);
        $objPHPExcel->getActiveSheet()->getProtection()->setInsertRows(true);
        $objPHPExcel->getActiveSheet()->getProtection()->setFormatCells(true);
        header("Cache-Control:must-revalidate,post-check=0,pre-check=0");
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="' . $filename . '"');
        header('Cache-Control: max-age=0');
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        Yii::app()->end();
    }
        /**
     * 导出到excel
     * @param type $tableHead excel表头与数库字段的对应,如 array('id'=>'ID序列号','name'='名称')
     * @param type $rowArray 数据列表数组
     */
    public function writerExcel($tableHead = array(), $rowArray = array(), $filename='excel.xls') {
        //error_reporting(E_ALL);
        $objPHPExcel = new PHPExcel ();
        $objPHPExcel->getProperties()->setCreator("店连店")->setLastModifiedBy("店连店")->setTitle("我的excel文档")->setSubject("文档导出测试")->setDescription("文档导出测试.")->setKeywords("office 2007 PHP数据")->setCategory("文档导出测试");
        $objPHPExcel->setActiveSheetIndex(0);
        $objPHPExcel->getActiveSheet()->setTitle('数据导出');
        $objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(15);
        $Letter = Array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z','AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ');
        $i = 0;
        foreach ($tableHead as $headKey => $headValue) {
            $objPHPExcel->getActiveSheet()->setCellValue("$Letter[$i]1", "$headValue");
            $i++;
        }
        $j = 2;
        foreach ($rowArray as $rows) {
            $i = 0;
            foreach ($tableHead as $headKey => $headValue) {
                $objPHPExcel->getActiveSheet()->setCellValueExplicit("$Letter[$i]$j", "$rows[$headKey]", PHPExcel_Cell_DataType::TYPE_STRING);
                $i++;
            }
            $j++;
        }
        $objPHPExcel->getActiveSheet()->getDefaultColumnDimension()->setWidth('20');
        $objPHPExcel->getActiveSheet()->getStyle('A1:AZ1')->getFont()->setBold(true);
        //    $objPHPExcel->getActiveSheet()->setCellValue('A2', "小风");
        //    $objPHPExcel->getActiveSheet()->setCellValue('B2', "wang");
        //    $objPHPExcel->getActiveSheet()->getCell('I')->setValueExplicit('861391327543258', PHPExcel_Cell_DataType::TYPE_STRING);
        //    $objPHPExcel->getActiveSheet()->setCellValue('G2', '2008-12-31');
        //    $objPHPExcel->getActiveSheet()->getStyle('G2')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDDSLASH);
        //    $time = gmmktime(0, 0, 0, 12, 31, 2008);
        //    $objPHPExcel->getActiveSheet()->setCellValue('G3', PHPExcel_Shared_Date::PHPToExcel($time));
        //    $objPHPExcel->getActiveSheet()->freezePane('A2');
       // $objPHPExcel->getActiveSheet()->getProtection()->setPassword('123123');
        $objPHPExcel->getActiveSheet()->getProtection()->setSheet(true);
        $objPHPExcel->getActiveSheet()->getProtection()->setSort(true);
        $objPHPExcel->getActiveSheet()->getProtection()->setInsertRows(true);
        $objPHPExcel->getActiveSheet()->getProtection()->setFormatCells(true);
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save($filename);
    }
    
    /*
     * 从EXCEL中读取数据，以数组返回   如：array(0=>array(0=>'姓名',1=>'性别'),1=>array(...))
     * $uploadFile  为服务器中的存储地址
     */
    public function getExcelDatas($uploadFile,$import_limit= 1000){

    		$objReader = PHPExcel_IOFactory::createReader('Excel5'); 
    		if ($objReader->canRead($uploadFile)){
	    		$objPHPExcel = $objReader->load($uploadFile);
	    		$sheet  = $objPHPExcel->getSheet(0);
	    		$objWorksheet = $objPHPExcel->getActiveSheet();
	    		$highestRow = $objWorksheet->getHighestRow();
	    		$highestColumn = $objWorksheet->getHighestColumn();
	    		$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn); //总列数
	    		$headTitle = array();
	    		$return_array =  $new_array = $tmp_repeat = array();
	    		if (!($highestRow-1 > $import_limit)){
	    		$i = $j = $k = 0;
	    		/*从excel中取数据*/
	    		  for ($row = 2;$row <= $highestRow;$row ++){  /*取行数据，默认从第2条取,第1条为标题*/
	    		  	 for ($col=0;$col <=$highestColumnIndex;$col++){
	    		  	 	$return_array[$i][] = $objWorksheet->getCellByColumnAndRow($col,$row)->getValue();
	    		  	 }
	    		  	 if (!empty($return_array[$i][2])){ /*去空*/
	    		  	 	
	    		  	 	/* 去重复*/
	    		  	 	if (!in_array($return_array[$i][2], $tmp_repeat)){
	    		  	 		$j++;
	    		  	 		if ($j > $import_limit){
	    		  	 			break;
	    		  	 		}
	    		  	 		$tmp_repeat[] = $return_array[$i][2];
	    		  	 		$new_array[$j] = $return_array[$i];
	    		  	 	}
	    		  	 } 
	    		  	 
	    		  	 $i++;
	    		  }
	    		  
	    		 unlink($uploadFile);
	    		 return $new_array;
	    		}
	    		echo UtilD::toJavaScriptAlert('文档中的数据超出指定数据 '.$import_limit.' 条，无法导入！',Yii::app()->createUrl('sms/customer/Import'));
	    		Yii::app()->end();
    		} else {
    			echo UtilD::toJavaScriptAlert('无法正确打开文档！',Yii::app()->createUrl('sms/customer/Import'));
    			Yii::app()->end();
    		}
    }
   /*
    * 从文本文件(.txt)中读取数据，以数组返回   如：array(0=>array(0=>'姓名',1=>'性别'),1=>array(...))
    * $uploadFile  为服务器中的存储地址
    */
    public function getTxtData($uploadFile,$import_limit= 1000){
    	$new_array = $tmp_repeat = array();
    	$j = 0;
    	try{
    		$str = fopen($uploadFile,'r');
    		while(!feof($str)){
    			$val = trim(fgets ($str));
    			if (!empty($val)){ /*去掉空*/
    				if (!in_array($val, $tmp_repeat)){
    					$j++;
    					if ($j > $import_limit){
    						break;
    					}
    					$tmp_repeat[] = $val;
    					$new_array[] = array('','',$val,'文本导入');
    				}
    			}
    			
    		}
    		fclose($str);
    		unlink($uploadFile);
    		return $new_array;
    	}catch (Exception $e){
    		echo UtilD::toJavaScriptAlert('无法正确打开文档！',Yii::app()->createUrl('sms/customer/Import'));
    		Yii::app()->end();
    	}
    	
    	
    }
    
}